using System;
using System.Data.OleDb;
using System.Windows.Forms;



namespace BRozklad
{
    class WorkWithDB
    {
        private int rsize;
        private int[,] rozklad;
        private static OleDbConnection connection = new OleDbConnection();
        public WorkWithDB()
        {
        }
        public static bool Open()
        {
            connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Configuration.DBFile;
            try
            {
                connection.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }
        public static bool Close()
        {
            try
            {
                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }
        public bool startWorkEraseCur()
        {
            string sqlStr = "DELETE * FROM CurPeople;";
            try
            {
                OleDbCommand command = new OleDbCommand(sqlStr, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }
        public bool InsertToCur(Rozklad inrozklad)
            
        {
            //TODO: bagato v odnomu zaprosi
            this.startWorkEraseCur();
            rozklad = inrozklad.GetRozklad();
            rsize = inrozklad.Ssize;
            string insStringStart = "INSERT INTO CurPeople (IDPeople,IDPara,IDGroupe, IDAuditoria,IDTeacher,IDTime, Kilk) VALUES (";
            string insStringFinish = " );";
            string insString;
            string inString;
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            try
            {
                for (int i = 0; i < rsize; i++)
                {
                    inString = "";
                    inString += Configuration.curentPeople;
                    inString += ", ";
                    inString += rozklad[i, 0];
                    inString += ", ";
                    inString += rozklad[i, 1];
                    inString += ", ";
                    inString += rozklad[i, 3];
                    inString += ", ";
                    inString += rozklad[i, 2];
                    inString += ", ";
                    inString += rozklad[i, 4];
                    inString += ", ";
//                    inString += (double)rozklad[i, 5]/2.0;
                    if (rozklad[i, 5] < 2)
                    {
                        inString += "0.5";
                    }
                    else
                    {
                        inString += "1.0";
                    }
                    insString = insStringStart + inString + insStringFinish;
                    command.CommandText = insString;
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }
        public int GetCount(string sqlStr)
        {
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = sqlStr;
            int res = 0;
            try
            {
                OleDbDataReader reader = command.ExecuteReader();
                if (!reader.Read())
                    return 0;
                if (reader.IsDBNull(0))
                    return 0;
                res = Convert.ToInt32(reader[0]);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n" + sqlStr, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
//            object ob = Convert.ToInt32(command.ExecuteScalar());
            return res;
        }
    }
}
